using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using Enttiies;


namespace Bll
{
    public class CategoryBll
    {
        private const string SQL_SELECT_CATGORIES = "select * from Categories";
        private const string SQL_INSERT_CATGORY = "insert into Categories(CategoryName, Description, Picture) values(@CategoryName, @Description, @Picture)";
        private const string SQL_UPDATE_CATGORY = "update Categories set CategoryName = @CategoryName, Description = @Description, Picture = @Picture where CategoryID = @CategoryID";
        private const string SQL_DELETE_CATGORY = "delete from Categories where CategoryID = @CategoryID";

        private const string PARM_CATEGORY_ID = "@CategoryID";
        private const string PARM_CATEGORY_NAME = "@CategoryName";
        private const string PARM_CATEGORY_DESCRIPTION = "@Description";
        private const string PARM_CATEGORY_PICTURE = "@Picture";

        private byte[] GetBytesFromImage(Image img)
        {
            byte[] bytes = null;
            if (img != null)
            {
                // Luu Image vao MemoryStream
                MemoryStream ms = new MemoryStream();
                img.Save(ms, img.RawFormat);
                // chuyen du lieu ve Image tu MemoryStream vao mang bytes
                bytes = ms.ToArray();
            }
            return bytes;
        }
        public IList<CategoryInfo> GetAll()
        {
            // viet lenh de truy van danh sacsh cac cattergories tu bang Categories
            IList<CategoryInfo> categories = new List<CategoryInfo>();
            // 
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_CATGORIES, null))
            {
                while (reader.Read())
                {
                    CategoryInfo cate = new CategoryInfo();
                    cate.CategoryID = reader.GetInt32(0);
                    cate.CategoryName = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        cate.Description = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                    {
                        byte[] bytes = (byte[])reader.GetSqlBinary(3);
                        ImageConverter imgConvert = new ImageConverter();
                        cate.Picture = (Image)imgConvert.ConvertFrom(bytes);
                    }

                    // truy van danh sach cac san pham (products) thuoc Category nay
                    // cate.Products = prodBll.GetAll(); .... 
                    // do it your self
                    // Hint:
                    // Dinh ngia lop nghiep vu ProductBll voi cac pt GetAll, Insert, Update...
                    categories.Add(cate);
                }
            }
            return categories;
        }

        public bool Insert(CategoryInfo cate)
        {
            
            // viet lenh de insert doi tuong CategoryInfo vao CSDL
            
            // Cach 1
            //bool result = false;
            //// Tao danh sach cac tham so
            //SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            //conn.Open();
            //SqlCommand cmd = new SqlCommand(SQL_INSERT_CATGORY, conn);

            //// Tao danh sach cac tham so cho doi tuong command
            //SqlParameter parm = new SqlParameter(PARM_CATEGORY_NAME, SqlDbType.NChar);
            //parm.Value = cate.CategoryName;
            //cmd.Parameters.Add(parm);

            //parm = new SqlParameter(PARM_CATEGORY_DESCRIPTION, SqlDbType.NText);
            //parm.Value = cate.Description;

            //parm = new SqlParameter(PARM_CATEGORY_PICTURE, SqlDbType.Image);
            //parm.Value = GetBytesFromImage(cate.Picture);
            //int insertRs = 0;
            //insertRs = cmd.ExecuteNonQuery();
            //result = (insertRs > 0 ? true : false);
            //if (result == true)
            //{
            //    // truy van de lay gia tri cot CategoryID (truong tu dong)
            //    string sql = "select @@IDENTITY";
            //    cmd = new SqlCommand(sql, conn);
            //    cate.CategoryID = int.Parse(cmd.ExecuteScalar());
            //}
            //conn.Close();
            //return result;

            // Cach 2
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            //// Tao danh sach cac tham so 
            SqlParameter[] parms = new SqlParameter[3];
            parms[0] = new SqlParameter(PARM_CATEGORY_NAME, SqlDbType.NChar);
            parms[0].Value = cate.CategoryName;
           

            parms[1] = new SqlParameter(PARM_CATEGORY_DESCRIPTION, SqlDbType.NText);
            parms[1].Value = cate.Description;

            parms[2] = new SqlParameter(PARM_CATEGORY_PICTURE, SqlDbType.Image);
            parms[2].Value = GetBytesFromImage(cate.Picture);
            bool result = (SQLDBHelper.ExecuteNonQuery(conn, CommandType.Text,
                SQL_INSERT_CATGORY, parms) > 0 ? true : false);
            if (result == true)
            {
                // truy van gia tri cot CategoryID
                string sql = "select @@IDENTITY";
                cate.CategoryID = int.Parse(SQLDBHelper.ExecuteScalar(conn, CommandType.Text,
                    sql, null).ToString());
                conn.Close();

            }

            return result;
        }

        public bool Update(CategoryInfo cate)
        {
            // viet lenh de update doi tuong CategoryInfo tu CSDL
            //// Tao danh sach cac tham so 
            SqlParameter[] parms = new SqlParameter[4];
            parms[0] = new SqlParameter(PARM_CATEGORY_NAME, SqlDbType.NChar);
            parms[0].Value = cate.CategoryName;


            parms[1] = new SqlParameter(PARM_CATEGORY_DESCRIPTION, SqlDbType.NText);
            parms[1].Value = cate.Description;

            parms[2] = new SqlParameter(PARM_CATEGORY_PICTURE, SqlDbType.Image);
            parms[2].Value = GetBytesFromImage(cate.Picture);

            parms[3] = new SqlParameter(PARM_CATEGORY_ID, SqlDbType.Int);
            parms[3].Value = cate.CategoryID;
           return (SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING, CommandType.Text,
                SQL_UPDATE_CATGORY, parms) > 0 ? true : false);
        }

        public bool Delete(CategoryInfo cate)
        {
            // viet lenh de delete doi tuong CategoryInfo khoi CSDL
            SqlParameter[] parms = new SqlParameter[1];
            
            parms[0] = new SqlParameter(PARM_CATEGORY_ID, SqlDbType.Int);
            parms[0].Value = cate.CategoryID;
            return (SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING, CommandType.Text,
                 SQL_DELETE_CATGORY, parms) > 0 ? true : false);
        }

    }
}
